#
# Fixed point types
#

# $extra_col_opts is set by a calling test
# $extra_col_opts_ddl_err is set by a calling test
# $extra_col_opts_dml_err is set by a calling test

if ($extra_col_opts_ddl_err == 0)
{
  eval CREATE TABLE t1 (
    d DECIMAL $extra_col_opts,
    d0 DECIMAL(0) $extra_col_opts,
    d1_1 DECIMAL(1,1) $extra_col_opts,
    d10_2 DECIMAL(10,2) $extra_col_opts,
    d60_10 DECIMAL(60,10) $extra_col_opts,
    n NUMERIC $extra_col_opts,
    n0_0 NUMERIC(0,0) $extra_col_opts,
    n1 NUMERIC(1) $extra_col_opts,
    n20_4 NUMERIC(20,4) $extra_col_opts,
    n65_4 NUMERIC(65,4) $extra_col_opts,
    pk NUMERIC $extra_col_opts PRIMARY KEY
  ) ENGINE=rocksdb;
}
if ($extra_col_opts_ddl_err == 1)
{
  # 5.7 uses strict mode by default, so this will now return an error
  --error ER_PRIMARY_CANT_HAVE_NULL
  eval CREATE TABLE t1 (
    d DECIMAL $extra_col_opts,
    d0 DECIMAL(0) $extra_col_opts,
    d1_1 DECIMAL(1,1) $extra_col_opts,
    d10_2 DECIMAL(10,2) $extra_col_opts,
    d60_10 DECIMAL(60,10) $extra_col_opts,
    n NUMERIC $extra_col_opts,
    n0_0 NUMERIC(0,0) $extra_col_opts,
    n1 NUMERIC(1) $extra_col_opts,
    n20_4 NUMERIC(20,4) $extra_col_opts,
    n65_4 NUMERIC(65,4) $extra_col_opts,
    pk NUMERIC $extra_col_opts PRIMARY KEY
  ) ENGINE=rocksdb;

  # 5.7 _requires_ a not NULL PK and can't mimick 5.6 behavior, so we are
  # adding in a fake key to satisfy and allow the test to resume
  eval CREATE TABLE t1 (
    d DECIMAL $extra_col_opts,
    d0 DECIMAL(0) $extra_col_opts,
    d1_1 DECIMAL(1,1) $extra_col_opts,
    d10_2 DECIMAL(10,2) $extra_col_opts,
    d60_10 DECIMAL(60,10) $extra_col_opts,
    n NUMERIC $extra_col_opts,
    n0_0 NUMERIC(0,0) $extra_col_opts,
    n1 NUMERIC(1) $extra_col_opts,
    n20_4 NUMERIC(20,4) $extra_col_opts,
    n65_4 NUMERIC(65,4) $extra_col_opts,
    pk NUMERIC $extra_col_opts,
    fake_key INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (fake_key)
  ) ENGINE=rocksdb;
}
SHOW COLUMNS IN t1;

# Always valid values

INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (100,123456,0.3,40000.25,123456789123456789.10001,1024,7000.0,8.0,999999.9,9223372036854775807,1);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (0,0,0,0,0,0,0,0,0,0,2);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (9999999999.0,9999999999.0,0.9,99999999.99,99999999999999999999999999999999999999999999999999.9999999999,9999999999.0,9999999999.0,9.0,9999999999999999.9999,9999999999999999999999999999999999999999999999999999999999999.9999,3);

--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;

# Values which can be valid or not,
# depending on whether columns are SIGNED or UNSIGNED
# (if not valid should produce warnings)

if ($extra_col_opts_dml_err == 0)
{
  INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807,4);
  INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999,5);
}
if ($extra_col_opts_dml_err == 1)
{
  # 5.7 uses strict mode by default, so this will now return an error
  --error ER_WARN_DATA_OUT_OF_RANGE
  INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807,4);
  --error ER_WARN_DATA_OUT_OF_RANGE
  INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999,5);

  # now test with the 5.6 behavior just to be sure
  set @orig_sql_mode=@@session.sql_mode;
  set session sql_mode="";
  INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807,4);
  INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999,5);
  set session sql_mode=@orig_sql_mode;
}

--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;

--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1 WHERE n20_4 = 9999999999999999.9999 OR d < 100;

# Invalid values

# 5.7 uses strict mode by default, so this will now return an error
--error ER_WARN_DATA_OUT_OF_RANGE
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  6
);

# now test with the 5.6 behavior just to be sure
set @orig_sql_mode=@@session.sql_mode;
set session sql_mode="";
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  9999999999999999999999999999999999999999999999999999999999999.9999,
  6
);
set session sql_mode=@orig_sql_mode;

--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;

# 5.7 uses strict mode by default, so this will now return an error
--error ER_WARN_DATA_OUT_OF_RANGE
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999,7);

# now test with the 5.6 behavior just to be sure
set @orig_sql_mode=@@session.sql_mode;
set session sql_mode="";
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999,7);
set session sql_mode=@orig_sql_mode;

--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;

# 5.7 uses strict mode by default, so this will now return an error
--error ER_WARN_DATA_OUT_OF_RANGE
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111,8);

# now test with the 5.6 behavior just to be sure
set @orig_sql_mode=@@session.sql_mode;
set session sql_mode="";
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4,pk) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111,8);
set session sql_mode=@orig_sql_mode;

--sorted_result
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;

--error ER_TOO_BIG_PRECISION
eval ALTER TABLE t1 ADD COLUMN n66 NUMERIC(66) $extra_col_opts;

--error ER_TOO_BIG_PRECISION
eval ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(66,6) $extra_col_opts;

--error ER_TOO_BIG_SCALE
eval ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(66,66) $extra_col_opts;

DROP TABLE t1;
